﻿package 
{
	import flash.events.SQLEvent;
	import flash.data.SQLConnection;
	import flash.data.SQLStatement;
	import flash.filesystem.File;
	import flash.events.MouseEvent;
	import flash.data.SQLResult;
	import flash.events.EventDispatcher;
	import flash.events.Event;
	
	public class Util extends EventDispatcher
	{
		private var conn:SQLConnection;
		private var sql:SQLStatement;
		public var res:Array = new Array();
		
		public function Util()
		{
			//create a new sql connection
			conn = new SQLConnection();
			//add an event handeler for the open event
			conn.addEventListener(SQLEvent.OPEN, createTables);
			//create the database if it doesn't exist, otherwise just opens it
			var dbFile:File=File.applicationDirectory.resolvePath("dranoidTest.db");
			conn.openAsync(dbFile);
			
		}

		private function createTables(event:SQLEvent):void {
			//create a new sql statement
			sql = new SQLStatement();
			//set the statement to connect to our database
			sql.sqlConnection=conn;
			//parse the sql command that creates the table if it doesn't exist
			sql.text= "CREATE TABLE IF NOT EXISTS Artiest (id INTEGER PRIMARY KEY UNIQUE, Naam TEXT, Info TEXT, Foto NONE, Website TEXT, Youtube TEXT, Myspace TEXT)";
			//call the execute function to execute our statement
			sql.execute();
					
			//Het zelfde voor de andere tabellen
			var sql4:SQLStatement = new SQLStatement();
			sql4.sqlConnection=conn;
			sql4.text = "CREATE TABLE IF NOT EXISTS Festivaldag (id INTEGER PRIMARY KEY AUTOINCREMENT,  Naam TEXT,  Opendeur TEXT,  Datum TEXT)";
			sql4.execute();
			
			//Het zelfde voor de andere tabellen
			var sql5:SQLStatement = new SQLStatement();
			sql5.sqlConnection=conn;
			sql5.text = "CREATE TABLE IF NOT EXISTS Optreden (id INTEGER PRIMARY KEY AUTOINCREMENT, Startuur TEXT, Einduur TEXT, Artiestid INTEGER, Festivaldagid INTEGER, Podiumid INTEGER)";
			sql5.execute();
			
			//Het zelfde voor de andere tabellen
			var sql6:SQLStatement = new SQLStatement();
			sql6.sqlConnection=conn;
			sql6.text = "CREATE TABLE IF NOT EXISTS Podium (id INTEGER PRIMARY KEY AUTOINCREMENT, Naam TEXT, Omschrijving TEXT)";
			sql6.execute();
			
			//Het zelfde voor de andere tabellen
			var sql7:SQLStatement = new SQLStatement();
			sql7.sqlConnection=conn;
			sql7.text = "CREATE TABLE IF NOT EXISTS UpdateApp (id INTEGER PRIMARY KEY AUTOINCREMENT, lastUpdate TEXT)";
			sql7.execute();
			
			dispatchEvent(new Event("INIT"));
					
		}
		
		public function add(n:String):void
		{
			// The statement (notice the words with :).
			sql.text = "INSERT INTO Artiest (name) VALUES (:name)";
			
			// Set the paramenters.
			sql.parameters[":name"] = n;
			sql.execute();
			sql.clearParameters(); // Better clear the parameters if we are going to reuse the statement.
			dispatchEvent(new Event("ADDED"));
		}
		
		public function retrieveData():void {
			//create a new sql statemant
			sql = new SQLStatement();
			sql.sqlConnection=conn;
			//this sql command retrieves all the fields from our
			//table in the database and orders it by name
			sql.text =  "SELECT a.Naam AS Naam, o.Startuur AS Startuur, o.Einduur AS Einduur, f.Naam AS Dag, p.Naam AS Podium FROM Optreden o, Artiest a, Festivaldag f, Podium p WHERE o.Artiestid = a.id and o.Festivaldagid = f.id and o.Podiumid = p.id ORDER BY a.naam";
			//add a new event listener if there is data
			//to display it. Wanneer er sql is uitgevoerd en dus data teruggeeft, run functie selecthandler
			
			sql.addEventListener(SQLEvent.RESULT, selectHandler);
			sql.execute();
			
		}
		
		public function zoeken(s:String):void {
			//create a new sql statemant
			sql = new SQLStatement();
			sql.sqlConnection=conn;
			//this sql command retrieves all the fields from our
			//table in the database and orders it by name
			sql.text =  "SELECT a.Naam AS Naam, o.Startuur AS Startuur, o.Einduur AS Einduur, f.Naam AS Dag, p.Naam AS Podium FROM Optreden o, Artiest a, Festivaldag f, Podium p WHERE o.Artiestid = a.id and o.Festivaldagid = f.id and o.Podiumid = p.id and a.Naam LIKE '%" + s + "%' ORDER BY a.naam";
			//add a new event listener if there is data
			//to display it. Wanneer er sql is uitgevoerd en dus data teruggeeft, run functie selecthandler
			
			sql.addEventListener(SQLEvent.RESULT, selectHandler);
			sql.execute();
			
		}
		
		public function updateFromXML(a:Array):void{
			
			for (var i:Number=0; i < a.length; i++){
			sql = new SQLStatement();
			sql.sqlConnection=conn;
										
			sql.text = "INSERT INTO Artiest (id, Naam, Info, Foto, Website, Youtube, Myspace) VALUES (:id, :Naam, :Info, :Foto, :Website, :Youtube, :Myspace)";
			
			
			sql.parameters[":id"] = i;
			sql.parameters[":Naam"] = a[i].getTitle();
			sql.parameters[":Info"] = a[i].getdescription();
			sql.parameters[":Foto"] = a[i].getimagesid() + "." + a[i].getextension();
			sql.parameters[":Website"] = a[i].getwebsite();
			sql.parameters[":Youtube"] = a[i].getyoutube();
			sql.parameters[":Myspace"] = a[i].getMyspace();
			sql.execute();
			sql.clearParameters();
			
			}
			
			
			dispatchEvent(new Event("Updated"));
		}

		public function selectHandler(event:SQLEvent):void {
			
			res = event.target.getResult().data;
			trace(res);
			
			dispatchEvent(new Event("COMPLETED"));
		}	
					
		public function getData():Array
		{
			return res;
		}
		
		


	}

}